package com.zhiyun.hxzy.dao;

import com.zhiyun.hxzy.domain.Classes;
import com.zhiyun.hxzy.domain.Homework;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Repository
public interface HomeWorkDao extends JpaRepository<Homework, Long>, JpaSpecificationExecutor<Homework> {

    List<Homework> findByTags(Long id);//根据标签id查询作业

    //根据学生id查询所有作业，checked属性是是否批改
    @Query(value = "select * from hx_homework WHERE id in(select hid from hx_student_homework where sid=?) order by time desc", nativeQuery = true)
    List<Homework> findByStudentId(Long id);

    //根据学生id查询所有作业，checked属性是是否批改
    @Query(value = "select * from hx_homework WHERE id in(select hid from hx_student_homework where sid=?) order by time desc", nativeQuery = true)
    Page<Homework> findByStudentId(Long id, PageRequest pageRequest);

    //分页根据学生id查询所有作业，checked属性是是否批改
    @Query(value = "SELECT * FROM hx_homework WHERE id IN(SELECT hid FROM hx_student_homework WHERE sid= ?1) AND checked = ?2 ORDER BY TIME DESC", nativeQuery = true)
    Page<Homework> findByStudentId(Long id, int checked, PageRequest pageRequest);

    //作业查找1-1所有班级
    @Query(value = "SELECT * FROM hx_classes WHERE schoolId=?", nativeQuery = true)
    List<Classes> findSchoolAllClasses(Long schoolId);

    //根据老师id查询所有作业，checked属性是是否批改
    @Query(value = "SELECT " + "  * " + "FROM " + "  hx_homework " + "WHERE id IN " + "  (SELECT " + "    hid " + "  FROM " + "    hx_student_homework " + "  WHERE sid IN " + "    (SELECT " + "      id " + "    FROM " + "      hx_student " + "    WHERE classId IN " + "      (SELECT " + "        classesId " + "      FROM " + "        hx_teacher_classes " + "      WHERE teacherId = ?1)) " + "    AND checked = ?2) " + "ORDER BY TIME DESC ", nativeQuery = true)
    Page<Homework> findHomeworkByTeacherId(Long teacherId, int checked, PageRequest pageRequest);


    @Query(nativeQuery = true,value = "SELECT \n" +
            "  * " +
            "FROM" +
            "  hx_homework " +
            "WHERE id IN " +
            "  (SELECT " +
            "    hid " +
            "  FROM" +
            "    hx_student_homework " +
            "  WHERE sid IN " +
            "    (SELECT " +
            "      id " +
            "    FROM" +
            "      hx_student " +
            "    WHERE classId =?1 )) AND checked =?2")
    Page<Homework> findHomeworkByTeacherIdAndClassId(Long classId,int checked,PageRequest pageRequest);

    //作业查找1-2所有作业
    @Query(value = "SELECT * FROM hx_homework WHERE id IN " + "(SELECT hid FROM hx_student_homework WHERE sid IN " + "(SELECT id FROM hx_student WHERE classId IN " + "(SELECT id FROM hx_classes WHERE schoolId=?)))", nativeQuery = true)
    List<Homework> findSchoolAllHomework(Long schoolId);

    //--作业查找2-查找某一班级内所有学生的所有作业
    @Query(value = "SELECT * FROM hx_homework WHERE id IN " + "(SELECT hid FROM hx_student_homework WHERE sid IN " + "(SELECT id FROM hx_student WHERE classId = ?))ORDER BY TIME DESC", nativeQuery = true)
    List<Homework> findByClassesId(Long classId);

    //所有作业里的最新作业
    @Query(value = "SELECT * FROM hx_homework WHERE checked=1 ORDER BY TIME DESC", nativeQuery = true)
    List<Homework> newestHomework();

    //一个班内所有学生的最新作业
    @Query(value = "SELECT * FROM hx_homework WHERE id IN (SELECT hid FROM hx_student_homework WHERE sid IN (SELECT id FROM hx_student WHERE classId = ?))ORDER BY TIME DESC", nativeQuery = true)
    List<Homework> newestHomework2(Long id);

    //查询所有作业
    @Query(value = "SELECT * FROM hx_homework WHERE checked=1", nativeQuery = true)
    List<Homework> findAll();

    //根据作业名查询
    @Query(value = "SELECT * FROM hx_homework WHERE workName=?1 and checked=1", nativeQuery = true)
    List<Homework> findAllByWorkName(String workName);

    //根据内容查询作业
    List<Homework> findByContent(String content);

    @Transactional
    @Query(value = "delete from hx_student_homework where sid = ? and hid = ? ", nativeQuery = true)
    @Modifying
    void deleteByStudentIdAndHomeworkId(Long studentId, Long homeworkId);//根据学生id和作业id删除关联表中的数据

    @Query(value = "SELECT * FROM hx_homework WHERE id IN(SELECT hid FROM hx_student_homework WHERE sid=(SELECT id FROM hx_student WHERE NAME =?)) order by time desc", nativeQuery = true)
    Page<Homework> findByStudentName(String studentName, PageRequest pageRequest);//根据学生名字查询学生的所有作业

    @Transactional
    @Modifying
    @Query(value = "DELETE FROM hx_student_homework WHERE hid = ?1", nativeQuery = true)
    void deleteHomeWorkStudent(Long hid);//根据作业id删除学生作业中间表的数据

    //根据老师id查询checked为0的作业
    @Query(nativeQuery = true,value = "select * from hx_homework where teacherId =?1 and checked = ?2 ")
    List<Homework> findByTeacherIdAndChecked(Long teacherId,int checked);



    @Query(value = "SELECT " +
            "  * " +
            "FROM " +
            "  hx_homework " +
            "WHERE id IN " +
            "  (SELECT " +
            "    hid " +
            "  FROM " +
            "    hx_student_homework " +
            "  WHERE sid IN " +
            "    (SELECT " +
            "      id " +
            "    FROM " +
            "      hx_student " +
            "    WHERE classId IN " +
            "      (SELECT " +
            "        classesId " +
            "      FROM " +
            "        hx_teacher_classes " +
            "      WHERE teacherId = ?1)) " +
            "    AND checked = ?2) " +
            "ORDER BY TIME DESC ", nativeQuery = true)
    List<Homework> f(Long teacherId,int checked);

}
